---
title: "SQL-PREL-02: Parent relationships of a child location"
summary: Retrieve parent provider locations related to a child location
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-08-21
library: SAIF.Business.View.Provider
reference_type: embedded
---

# SQL-PREL-02 Parent relationships of a child location

Retrieves the parent provider locations related to a given child provider location through
`PROVIDER_RELATIONSHIP`, returning the parent location's full detail columns and the
relationship's effective/termination dates and code.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-PREL-02 |
| **Operation type** | Read |
| **Execution method** | Inline SQL (BOF criteria constant) |
| **Database** | Oracle |
| **Schema** | `[unqualified]**` |
| **Command type** | Text |
| **Business object** | `clsProviderRelationshipCollection` |
| **Criteria property** | `RetrieveParentRelationshipsSQL` |
| **Lifecycle method** | `BeforeRetrieve` / `AfterRetrieve` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:ProviderLocationID` | Input | Decimal | N/A | Child location key; returns parent relationships |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [PROVIDER_LOCATION](../database-tables.md#provider_location) | JOIN | `PROVIDER_LOCATION_SEQ_NUM` and full detail columns (`APPROVED_FLAG`, `BUSINESS_NAME`, `BUSINESS_FLAG`, etc.) |
| [PROVIDER_RELATIONSHIP](../database-tables.md#provider_relationship) | JOIN | `PROVIDER_LOCATION_SEQ_NUM`, `PARENT_PROVIDER_LOC_SEQ_NUM`, `EFF_DATE`, `EXP_DATE`, `PROVIDER_RELATIONSHIP_CODE` |

## Sample SQL

```sql
-- Parent relationships of a child location
SELECT
  PR.Provider_Location_Seq_Num           AS PRProviderLocationID,
  PR.PARENT_PROVIDER_LOC_SEQ_NUM         AS ParentProviderLocationID,
  PR.EFF_DATE                            AS StartDate,
  PR.EXP_DATE                            AS TerminationDate,
  PR.PROVIDER_RELATIONSHIP_CODE          AS ProviderRelationshipCode,
  parentPL.APPROVED_FLAG                 AS IsApproved,
  parentPL.APPROVED_USER                 AS ApprovedUser,
  parentPL.BUSINESS_FLAG                 AS IsBusiness,
  parentPL.CRITICAL_ACCESS_HOSPITAL_FLAG AS IsCriticalAccessHospital,
  parentPL.BUSINESS_NAME                 AS BusinessName,
  parentPL.FAX_NUM                       AS FaxNum,
  parentPL.FIRST_NAME                    AS FirstName,
  parentPL.INELIGIBILITY_FLAG            AS IsIneligible,
  parentPL.LAST_NAME                     AS LastName,
  parentPL.MIDDLE_NAME                   AS MiddleName,
  parentPL.PAYEE_NAME                    AS PayeeName,
  parentPL.PHONE_AREA_CODE               AS PhoneAreaCode,
  parentPL.PHONE_NUM                     AS PhoneNum,
  parentPL.PHONE_NUM_EXT                 AS PhoneNumExt,
  parentPL.POSSIBLE_FRAUD_FLAG           AS IsPossibleFraud,
  parentPL.PRIMARY_LOCATION_FLAG         AS IsPrimaryLocation,
  parentPL.PROVIDER_LOCATION_SEQ_NUM     AS ProviderLocationID,
  parentPL.PROVIDER_SEQ_NUM              AS ProviderID,
  parentPL.PROVIDER_TYPE_CODE            AS ProviderTypeCode,
  parentPL.RECENT_ACTIVITY_FLAG          AS HasRecentActivity,
  parentPL.TOLL_FREE_FAX_NUM             AS TollFreeFaxNum,
  parentPL.TOLL_FREE_PHONE_NUM           AS TollFreePhoneNum,
  parentPL.UPDATE_DATE                   AS UpdateDate,
  parentPL.UPDATE_NUM                    AS UpdateNum,
  parentPL.UPDATE_USER                   AS UpdateUser,
  parentPL.WEB_SITE_ADDRESS              AS WebSiteAddress,
  parentPL.EMAIL_ADDR                    AS EmailAddress
FROM provider_location pl
  JOIN provider_relationship pr
    ON pr.provider_location_seq_num = PL.provider_location_seq_num
  LEFT JOIN provider_location parentPL
    ON parentPL.provider_location_seq_num = pr.parent_provider_loc_seq_num
WHERE pl.provider_location_seq_num = :ProviderLocationID
ORDER BY parentPL.BUSINESS_NAME
```

> **Note:** `RetrieveSQL` returns the *child* statement when `SourceRelationshipType` is
> `Parent` (the `Case Else` branch) and the *parent* statement when it is `Child`.*

## Operations and processes
- [Load provider relationships](../operations/load-provider-relationships.md)

## Business rules
- [BR-PREL-001: Works-for relationship code](../business-rules.md#br-prel-001-works-for-relationships-are-wrk-code)
- [BR-PREL-002: Active-only works-for filter](../business-rules.md#br-prel-002-works-for-defaults-to-active-relationships-only)
- [BR-PREL-003: Relationship active window](../business-rules.md#br-prel-003-relationship-active-date-determination)
- [BR-PREL-004: Reciprocal relationship direction](../business-rules.md#br-prel-004-destination-relationship-type-is-the-opposite-of-the-source)

> **Note:** Statements marked with * were inferred from code structure, naming conventions, or
> contextual evidence where direct confirmation was not available in source artifacts. Statements
> marked with ** could not be confirmed; treat as unknown until verified.

## Sources
- SAIF.Business.View.Provider/clsProviderRelationshipCollection.vb